Couchbase
Querying Overview
Qarbine supports Couchbase queries enabled by the underlying Couchbase Node.js driver. In general this means any SQL++ query along with any data structures that may be returned.
Shown below is the Data Source Designer using a Couchbase data service and accessing the sample Couchbase beer content.
Some of the query results are shown below
Here is a query with a WHERE clause.
Here is a query with a more involved WHERE clause.
Vector Searching
Overview
See https://docs.couchbase.com/server/current/vector-search/run-vector-search-ui.html
Setting up Sample Data
Navigate to the cluster.
Click
Click
Determine the index of interest.
On the far right click the highlighted image.
A general search entry page is shown.
Enter the vector search query
{
"fields": ["*"],
"query": {
"match_none": ""
},
"knn": [
{
"k": 2,
"field": "colorvect_l2",
"vector": [ 0, 0, 128 ]
}
]
}
For example
By using the special match_none query in the query field, the Search query is only a Vector Search query. It only returns the k number of similar vectors. The Search Service combines the Vector search results from a knn object with the traditional query object by using an OR function. If the same documents match the knn and query objects, the Search Service ranks those documents higher in search results.
Click
The results are shown
To see the full row click the highlighted id text value.
The dialog includes content such as
{
"brightness": 14.592,
"color": "navy",
"colorvect_l2": [ 0 , 0, 128 ],
"description": "Navy is a deep, rich color that exudes sophistication....",
"embedding_model": "text-embedding-ada-002-v2",
"embedding_vector_dot": [...]
"id": "#000080",
"verbs": [ "deep", "rich", "sophisticated" ],
"wheel_pos": "other"
}
Using SQL++ Vector Searches
For details see the following links.
A framework for a vector query is shown below/
SELECT *, meta().id FROM <key_space>
WHERE anOptionalCondition
AND SEARCH(<key_space>, {"query": {"match": "content", "field": "text"}, "knn": {"vector": <vector_embedding>", "field": "vector_field", "k": 5}});
The data source query specification can be
#pragma pullFieldsUp rgb
SELECT *, meta().id
FROM rgbBucket.rgbScope.rgb
where SEARCH( rgbScope.rgb,
{
"fields": ["*"],
"query": { "match_none": "" },
"knn": [
{
"k": 5,
"field": "colorvect_l2",
"vector": [ 0, 0, 128 ]
}
]
}
)
limit 5
In this example specification we chose to pull the rgb field’s values up a level to the main level.
Vector Search Index References
For information on creating a vector search see
For more information see
Creating a sample vector search.
Click Add
Click
Reviewing the Generated Specification
You can enter criteria of the form “EXPLAIN SELECT ….” to have the SQL statement processed and have the returned answer set be the query just prior to send it to Couchbase.
explain
select * from _default
where country = "United States" and
state = @state order by name limit 25
This query has a variable which is prompted for.
Select the single result element and its details are shown to the right.
A convenient way of specifying this is to have “explain” on the first line and the rest of your SQL on the next lines.
explain
select * from _default
where country = "United States" and
state = @state order by name limit 25
Then simply “comment out” the first line when not in use
// explain
select * from _default
where country = "United States" and
state = @state order by name limit 25
You can also use “explain: true” in the JSON query specification for similar information.
Another way to get the specification is to press ALT and click 
. Below is a sample result.
Any “explain SELECT” or “explain: true” takes precedence over the ALT-click interaction.
Analytics
Sample data information can be found at
Added suggested index after running the query at
Search Considerations
NOTE- In general simply use the SEARCH() function with the SQL++ interface.
There are two Couchbase APIs for querying search at the scope level:
- cluster.searchQuery() supports FTS and
- cluster.search() supports FTS and vector search.
For more information see
https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/searchfun.html
https://docs.couchbase.com/nodejs-sdk/current/howtos/full-text-searching-with-sdk.html
In June 2024 it was noticed that indices mentioned in tutorial were missing. To define the missing travel-sample indices see the information at
and
JSON Query Specification
Qarbine supports using a JSON specification for retrieving data. The format is
{
useSearch: true | false,
bucket: aString,
scope: aString,
index: aString, ← required.
other fields per Couchbase documentation
}
The useSearch flag determines whether the search or query endpoint is used. Depending on how the Qarbine administrator defines the chosen Couchbase Data Service the endpoint used will be either HTTP or HTTPS oriented.
The bucket argument is not required if it was specified in the data service’s database entry field. The same holds for the scope argument. The effective query path is
api/bucket/${bucketName}/scope/${scopeName}/index/${indexName}/query
If you use the default search result sorting of _score, a document’s score determines where it appears in your search results.
For more information on the search specification see
https://docs.couchbase.com/server/current/search/search-request-params.html
GeoJSON Examples
See
https://docs.couchbase.com/server/7.2/fts/fts-supported-queries-geojson-spatial.html#prerequisites-dataset
Note that the conversion we used was explicit on the target collection.
UPDATE `travel-sample`..inventory.airport
SET geojson = { "type": "Point", "coordinates": [geo.lon, geo.lat] }
WHERE geo IS NOT null;
UPDATE `travel-sample`.inventory.airport
SET geoarea = { "coordinates": [geo.lon, geo.lat], "type": "circle", "radius": "10mi"}
WHERE geo IS NOT null AND type="airport";
The geospatial querying discussion starts at
https://docs.couchbase.com/server/7.2/fts/fts-supported-queries-geojson-spatial.html#querying-the-geojson-spatial-fields
The Geo JSON indexing discussion starts at
https://docs.couchbase.com/server/7.2/fts/fts-supported-queries-geojson-spatial.html#creating_a_geojson_index
See also https://docs.couchbase.com/server/current/search/geo-search-rest-api.html
https://docs.couchbase.com/server/7.2/fts/fts-supported-queries-geo-point-distance.html
As of 1 June 2024 it seemed out of date. The following selections were made.
The click the Add button.
A similar interaction was done for the geoarea column as shown below.
The result is shown below.
The click
The result is
Qarbine Virtual Queries
There are a few convenience queries which are mainly DBA oriented. These queries are recognized by the Qarbine driver and provide common database information.
| Query | Description |
|---|---|
| list databases | Return a list of databases. |
| list collections | Return a list of Couchbase collections. |
See the “DBA Productivity” section of the online documentation for more details.
Troubleshooting
Query Validation
If a query fails within Qarbine then testis in the Capella web console. This is located at the highlighted tabs below.
For details on Couchbase querying see
Names with Dashes
Couchbase supports referencing a data holder using “bucket.scope.collection”. For example,
Note that the bucket name above is enclosed in tick marks and not quotes. An example of a Couchbase JOIN is shown below.
SELECT * FROM `travel-sample`.inventory.route r
JOIN `travel-sample`.inventory.airline a ON r.airlineid = META(a).id
WHERE a.country = 'France'